package org.alex.sample.controller.converter;

import java.util.HashMap;
import java.util.Map;

import org.alex.sample.utils.MD5;

public class Bysql {

	public static void main(String[] args) {
		//清除表数据
		System.out.println("TRUNCATE exam_role;");
		System.out.println("TRUNCATE exam_subject;");
		System.out.println("TRUNCATE exam_test;");
		System.out.println("TRUNCATE exam_test_type;");
		System.out.println("TRUNCATE exam_user;");
		System.out.println("TRUNCATE exam_user_role;");
		System.out.println("TRUNCATE exam_user_subject;");
		
		//exam_user　用户表
		System.out.println("INSERT INTO exam_user(user_name,name,password,tel,email,address,birthday,gender," + 
				"last_lock_time,login_fail_count) VALUE('jiaoshi','教师','" + MD5.getMD5Code("123456") + 
				"','13800138000','123456@126.com','中山职业技术学院','2018-01-20',1,'2016-01-01 00:00:00',0" + ");");
		System.out.println("INSERT INTO exam_user(user_name,name,password,tel,email,address,birthday,gender," + 
				"last_lock_time,login_fail_count) VALUE('guangliyuan','管理员','" + MD5.getMD5Code("123456") + 
				"','13800138000','123456@126.com','中山职业技术学院','2018-01-20',0,'2016-01-01 00:00:00',0" + ");");
		for(int i = 3; i <= 103; i++) {
			int k = i % 2 != 0?0:1;
			String sql = "INSERT INTO exam_user(user_name,name,password,tel,email,address,birthday,gender," + 
		"last_lock_time,login_fail_count) VALUE('student" + i + "','学生" + i + "号','" + MD5.getMD5Code("123456") + 
		"','13800138000','123456@126.com','中山职业技术学院','2018-01-20'," + k + ",'2016-01-01 00:00:00',0" + ");";
			System.out.println(sql);
		}
		
		//exam_user_subject　用户与科目表
		for(int k = 1; k <= 15; k++) {
			Map<Integer,Integer> userId = new HashMap<Integer,Integer>(); //防止用户ID重复
			for(int i = 1; i <= 30; i++) {
				int user_id = (int)((Math.random() * 100) + 3); //3-103的随机数
				if(userId.containsKey(user_id)) {
					continue;
				}
				userId.put(user_id, user_id);
				String sql = "INSERT INTO exam_user_subject(user_id,sub_id) VALUE(" + user_id + "," + k + ");";
				System.out.println(sql);
			}
		}
		
		//exam_role
		System.out.println("INSERT INTO exam_role(name) VALUE('学生');");
		System.out.println("INSERT INTO exam_role(name) VALUE('教师');");
		System.out.println("INSERT INTO exam_role(name) VALUE('管理员');");
		
		//用试题计算科目总分数
		int[] score  = new int[15];
		for(int s : score) {
			score[s] = 0;
		}
		
		//科目关联的试题
		int[] test = new int[15];
		for(int t : test) {
			test[t] = 0;
		}
		
		//exam_test　试题表
		for(int i = 1; i <= 1300; i++) {
			int sub_id = (int) ((Math.random() * 15) + 1); //1-15的随机数
			int test_type_id = (int)(Math.random() + 1.5); //1-2的随机数
			switch (sub_id) {
				case 1:
					score[0] += test_type_id;
					test[0]++;
					break;
				case 2:
					score[1] += test_type_id;
					test[1]++;
					break;
				case 3:
					score[2] += test_type_id;
					test[2]++;
					break;
				case 4:
					score[3] += test_type_id;
					test[3]++;
					break;
				case 5:
					score[4] += test_type_id;
					test[4]++;
					break;
				case 6:
					score[5] += test_type_id;
					test[5]++;
					break;
				case 7:
					score[6] += test_type_id;
					test[6]++;
					break;
				case 8:
					score[7] += test_type_id;
					test[7]++;
					break;
				case 9:
					score[8] += test_type_id;
					test[8]++;
					break;
				case 10:
					score[9] += test_type_id;
					test[9]++;
					break;
				case 11:
					score[10] += test_type_id;
					test[10]++;
					break;
				case 12:
					score[11] += test_type_id;
					test[11]++;
					break;
				case 13:
					score[12] += test_type_id;
					test[12]++;
					break;
				case 14:
					score[13] += test_type_id;
					test[13]++;
					break;
				case 15:
					score[14] += test_type_id;
					test[14]++;
					break;
				default:
					break;
			}
			String answer = test_type_id == 1?"A":"BD";
			String sql = "INSERT INTO exam_test(content,choose_a,choose_b,choose_c,choose_d,sub_id,test_type_id,answer) " 
		+ " VALUE('测试题目" + i + "','选项A','选项B','选项C','选项D'," + sub_id + "," + test_type_id + ",'" + answer + "');";
			System.out.println(sql);
		}
		
		//exam_subject　科目表
		for(int i = 1; i <= 15;i++) {
			int status = i % 2 != 0?0:1;
			String sql = "INSERT INTO exam_subject(name,description,test_count,total_time,total_score,status) VALUE(" + 
		"'测试科目" + i + "','测试科目" + i + "'," + test[i - 1] + ",90," + score[i - 1] + ","  + status + ");";
			System.out.println(sql);
		}
		
		//exam_test_type　试题类型表
		System.out.println("INSERT INTO exam_test_type(test_type,test_type_score) VALUE('单选题',1);");
		System.out.println("INSERT INTO exam_test_type(test_type,test_type_score) VALUE('多选题',2);");
		
		//exam_user_role　用户权限表
		System.out.println("INSERT INTO exam_user_role(role_id,user_id) VALUE(2,1);");
		System.out.println("INSERT INTO exam_user_role(role_id,user_id) VALUE(3,2);");
		for(int i = 3; i <= 103; i++) {
			System.out.println("INSERT INTO exam_user_role(role_id,user_id) VALUE(1," + i +");");
		}

	}

}
